set doc off
set verify off
--
--    Program: CAPGLEXT_PERIOD.sql
--    Description:
--    Extract GL period information 
--
--    Revision:
--    1st     
--    Parameter: 1. set of books short name e.g. SOB_C999


--whenever sqlerror exit failure rollback
set serveroutput on size 1000000;

declare   
  l_count			number;
  l_processed_count	number;
  l_rejected_count	number;
  l_err_msg       	varchar2(2000);
  
  l_utl_file  		utl_file.file_type;
  l_filename  		varchar2(255);
  l_pathname  		varchar2(100);

  l_ngo_sob			varchar2(20);
  l_set_of_books_id	number;
  
  cursor cur_cal (p_sob_id number) is
    select * 
    from   gl_period_statuses 
    where  set_of_books_id = p_sob_id;

begin
  l_pathname := '/tmp';
  l_filename := 'CAPGL_PERIOD.dat';
  l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
  
  l_count := 0;
  l_processed_count := 0;
  l_rejected_count  := 0;
  
  l_ngo_sob := '&1';
  
  begin
  	select set_of_books_id
  	into   l_set_of_books_id 
  	from   gl_sets_of_books 
  	where  upper(short_name) = upper(l_ngo_sob);
  exception
    when others then
      dbms_output.put_line(l_ngo_sob||': set of books not found');    
  end;  
     
  for cur in cur_cal(l_set_of_books_id) loop 	
          
    -- output data
    utl_file.put_line( l_utl_file, 
      cur.APPLICATION_ID                 ||chr(9)||
      cur.SET_OF_BOOKS_ID                ||chr(9)||
      cur.PERIOD_NAME                    ||chr(9)||
      to_char(cur.LAST_UPDATE_DATE, 'DD-MON-YYYY HH24:MI:SS') ||chr(9)||
      cur.LAST_UPDATED_BY                ||chr(9)||
      cur.CLOSING_STATUS                 ||chr(9)||
      cur.START_DATE                     ||chr(9)||
      cur.END_DATE                       ||chr(9)||
      cur.YEAR_START_DATE                ||chr(9)||
      cur.QUARTER_NUM                    ||chr(9)||
      cur.QUARTER_START_DATE             ||chr(9)||
      cur.PERIOD_TYPE                    ||chr(9)||
      cur.PERIOD_YEAR                    ||chr(9)||
      cur.EFFECTIVE_PERIOD_NUM           ||chr(9)||
      cur.PERIOD_NUM                     ||chr(9)||
      cur.ADJUSTMENT_PERIOD_FLAG         ||chr(9)||
      to_char(cur.CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') ||chr(9)||
      cur.CREATED_BY                     ||chr(9)||
      cur.LAST_UPDATE_LOGIN              ||chr(9)||
      cur.ELIMINATION_CONFIRMED_FLAG     ||chr(9)||
      cur.ATTRIBUTE1                     ||chr(9)||
      cur.ATTRIBUTE2                     ||chr(9)||
      cur.ATTRIBUTE3                     ||chr(9)||
      cur.ATTRIBUTE4                     ||chr(9)||
      cur.ATTRIBUTE5                     ||chr(9)||
      cur.CONTEXT                        ||chr(9)||
      cur.CHRONOLOGICAL_SEQ_STATUS_CODE
    );
    
    l_processed_count := l_processed_count + 1;  
  end loop;
  
  if l_processed_count = 0 then
    l_rejected_count := l_rejected_count + 1;
    l_err_msg := l_ngo_sob || ': no calendar defined for the selected set of books or no such set of books';
  end if;
         
  -- insert into log table
  insert into capcnv_log ( prog_id, process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPGLEXT_PERIOD', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
  
  commit;
  
  utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/

--exit
